﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Core.Entity;
using System.Data;

namespace Core.Dao
{
    class OrderDao
    {
        public bool insert(Order order)
        {
            object[,] param = {
                {"@OrderStatusId", order.OrderStatusId},
                {"@StaffId", order.StaffId},
                {"@AccountId", order.AccountId},
                {"@Date", order.Date},
                {"@Desc", order.Desc},
            };
            String sql = "insert into ORDERS(OST_ID, STF_ID, ACC_ID, ORD_DATE, ORD_DESC) " +
                "values(@OrderStatusId, @StaffId, @AccountId, @Date, @Desc)";
            return DB.exec(sql, param);
        }

        public bool update(Order order)
        {
            object[,] param = {
                {"@OrderStatusId", order.OrderStatusId},
                {"@StaffId", order.StaffId},
                {"@AccountId", order.AccountId},
                {"@Date", order.Date},
                {"@Desc", order.Desc},
                {"@id", order.Id},
            };
            String sql = "update ORDERS set OST_ID = @OrderStatusId, STF_ID = @StaffId, ACC_ID = @AccountId, ORD_DATE = @Date, ORD_DESC = @Desc "+
                "where ORD_ID = @id";
            return DB.exec(sql, param);
        }

        public bool delete(int id)
        {
            object[,] param = {
                {"@id", id}   
            };
            String sql = "delete from ORDERS where ORD_ID = @id";
            return DB.exec(sql, param);
        }

        public bool delete(String ids)
        {
            object[,] param = {
                {"@ids", ids}   
            };
            String sql = "delete from ORDERS where ORD_ID in (" + ids + ")";
            return DB.exec(sql);
        }

        public DataTable get(int id)
        {
            object[,] param = {
                {"@id", id}   
            };
            String sql = "select * from ORDERS where ORD_ID = @id";
            return DB.getData(sql, param);
        }

        public DataTable getAll()
        {
            String sql = "select * from ORDERS order by ORD_DATE";
            return DB.getData(sql);
        }

        public DataTable getAllOrderLinesByAccountIdAndServiceId(int accountId, int serviceId)
        {
            object[,] param = {
                {"@accountId", accountId},
                {"@serviceId", serviceId}
            };
            String sql = "select distinct ORL.*, SVC.SVC_NAME from ORDERS ORD, ORDER_LINES ORL, SERVICES SVC " +
                "where ORD.ORD_ID = ORL.ORD_ID and ORL.SVC_ID = SVC.SVC_ID " +
                "and ORD.ACC_ID = @accountId and SVC.SVT_ID = @serviceId and ORD.OST_ID = 4 " +
                "order by SVC.SVC_NAME";
            return DB.getData(sql, param);
        }
    }
}
